package com.woniuxy.shop.dao;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.woniuxy.shop.exception.ConnectMySQLException;
import com.woniuxy.shop.exception.DaoException;

//import dao.DbHelper;


/**
*@author 作者：
*version 创建时间：
*类说明：数据库联系工具
*/
public class DbHelper {
	
	private static ThreadLocal<Connection> cons = new ThreadLocal<>();
	/**
	 * 获取连接
	 * @return
	 */
	public static Connection getConnection() {
		// 加载驱动,在java8可省略
		// 获取连接
		Connection con =cons.get();
		if(con==null) {
			BufferedReader reader=null;
			try {
				String path = "shop.properties";
				InputStream ins = DbHelper.class.getClassLoader().getResourceAsStream(path);
				Properties p = new Properties();
//				reader = new BufferedReader(new FileReader(new File("shop.properties")));
				p.load(ins);
				String url = p.getProperty("url");
				String driverclass = p.getProperty("driverclass");
				String user = p.getProperty("user");
				String password = p.getProperty("password");
				
				Class.forName(driverclass);
//				Class.forName("com.mysql.cj.jdbc.Driver");
				con = DriverManager.getConnection(url,user,password);
				
//				con = DriverManager.getConnection("jdbc:mysql://localhost:3306/woniushop?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&autoReconnect=true","root", "password");
				cons.set(con);
			} catch (SQLException e) {
				e.printStackTrace();
				throw new ConnectMySQLException("sql连接异常", e);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				throw new ConnectMySQLException("类加载异常异常", e);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new ConnectMySQLException("IO异常", e);
			}finally {
				if(reader!=null) {
					try {
						reader.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						throw new ConnectMySQLException("IO异常", e);
					}
				}
			}
		}
		return con;
	}
	
	/**
	 * 关闭连接
	 * @param con
	 */
	public static void close(Connection con) {
		try {
			if (con != null && !con.isClosed()) {
				con.close();
				cons.remove();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new ConnectMySQLException("sql关闭连接异常", e);
		}
	}
	
	/**
	 * 执行增删改等语句
	 * @param sql
	 * @param params
	 */
	public static void excuteSQL(String sql, Object...params) {
		QueryRunner runner = new QueryRunner();
		Connection con = getConnection();//获取连接
		try {
			runner.execute(con,sql, params);//执行sql语句
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("执行sql:"+sql+"异常",e);
		}
	}
	
	public static void executeBatchSQL(String sql, Object[][] params) {
		QueryRunner runner = new QueryRunner();
		Connection con = getConnection();
		try {
			runner.batch(con, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("执行sql:"+sql+"异常",e);
		} 
	} 
	
	/**
	 * 前提：列名与属性名一致 执行select语句
	 * 执行多行多列查询语句
	 * @param <T>
	 * @param sql
	 * @param t
	 * @param params
	 * @return
	 */
	public static <T> List<T> executeQuery(String sql, Class<T> c, Object... params) {
		QueryRunner runner = new QueryRunner();
		Connection con = getConnection();
		List<T> result = null;
		try {
			result = runner.query(con, sql, new BeanListHandler<T>(c), params);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("执行sql:"+sql+"异常",e);
		} 
		return result;
	}
	
	/**
	 * 执行单行查询语句
	 * @param <T>
	 * @param sql
	 * @param c
	 * @param params
	 * @return
	 */
	public static <T> T executeQueryForSingleRow(String sql, Class<T> c, Object... params) {
		QueryRunner runner = new QueryRunner();
		Connection con = getConnection();
		T result = null;
		try {
			result = runner.query(con, sql, new BeanHandler<T>(c), params);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("执行sql:"+sql+"异常",e);
		} 
		return result;
	}
	
	/**
	 * 执行单行单列查询语句
	 * @param <T>
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> T executeQueryForScalar(String sql, Object... params) {
		QueryRunner runner = new QueryRunner();
		Connection con = getConnection();
		T result = null;
		try {
			result = runner.query(con, sql, new ScalarHandler<T>(), params);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("执行sql:"+sql+"异常",e);
		} 
		return result;
	}
}
